# -*-encoding: utf-8 -*-

from celery import Celery
from yxproject.views.utils.DB import MySqlCRUD
import logging, time

app = Celery('UploadTasks', broker='redis://:lczgywzyy@localhost:16379/1')
log = logging.getLogger('UploadTasks')

@app.task
def uploadImgByQueue(md5, path, tel, category, name, material, size, weight, aperture, price, description, url):
    timeStamp = time.time()
    ######查询图片本身是否存在
    #查询语句，查询该图片md5是否存在记录；
    imgExitsSQL = '''
        SELECT material_info.MD5 FROM material_info
            WHERE material_info.MD5 = %s;
    '''
    imgExistsResult = MySqlCRUD.selectImpl(imgExitsSQL, md5)
    if imgExistsResult:
        #查询当前图片是否与别人有关联，如果有关联则不能更新信息；否则则允许更新信息
        material_user_exists_SQL = '''
            SELECT user_image_info.MD5 FROM user_image_info
                WHERE user_image_info.MD5 = %s;
        '''
        isMaterialUserExists = MySqlCRUD.selectImpl(material_user_exists_SQL, md5)
        if isMaterialUserExists:
            log.debug("[EXISTS]上传的图片已经存在！")
        else:
            updateSQL = '''
                UPDATE material_info
                    SET category = %s,
                        name = %s,
                        material = %s,
                        size = %s,
                        weight = %s,
                        aperture = %s,
                        price = %s,
                        url = %s,
                        description = %s
                    WHERE md5 = %s;
            '''
            resultUpdate = MySqlCRUD.updateImpl(updateSQL, category, name, material, size, weight, aperture, price, url, description, md5)
            log.debug("[EXISTS]已更新！")
    else:
        #imgInsertSQL = '''
        #    INSERT INTO material_info (MD5, category, name, type, material, size, weight, aperture, price, description)
        #        SELECT %s, %s, %s, type, %s, %s, %s, %s, %s, %s FROM user_info
        #            WHERE user_info.email = %s;
        #'''
        #resultImgInsert = MySqlCRUD.insertImpl(imgInsertSQL, md5, category, name, material, size, weight, aperture, price, description, email)
        imgInsertSQL = '''
            INSERT INTO material_info (MD5, path, category, name, material, size, weight, aperture, price, description, url)
                VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        '''
        resultImgInsert = MySqlCRUD.insertImpl(imgInsertSQL, md5, path, category, name, material, size, weight, aperture, price, description, url)
        log.debug('[DONE] AFFECTED_ROW[' + str(resultImgInsert) +']')

    ######做关联记录
    #如果存在记录，判断路径是否需要更新。
    #sSQL = '''
    #    SELECT user_image_info.path, user_image_info.user_id FROM user_image_info
    #        INNER JOIN user_info
    #        ON user_info.ID = user_image_info.user_id
    #        WHERE user_image_info.MD5 = %s
    #            AND user_info.email = %s;
    #'''
    sSQL = '''
        SELECT material_info.path, user_image_info.user_id FROM user_image_info
            INNER JOIN user_info
            ON user_image_info.user_id = user_info.ID
            INNER JOIN material_info
            ON user_image_info.MD5 = material_info.MD5
            WHERE user_image_info.MD5 = %s
                AND user_info.tel = %s;
    '''

    #resultSelect = MySqlCRUD.selectImpl(sSQL, '096241b4c7bf2e59f5b3e5e9f2bfcaed', 'xyq547133@163.com')
    resultSelect = MySqlCRUD.selectImpl(sSQL, md5, tel)
    if resultSelect:
        vPath = resultSelect[0]['path']
        vUserID = resultSelect[0]['user_id']
        #如果查询出结果，并且路径一致，则不需要更新；否则，需要更新路径。
        if vPath == path:
            log.debug('[PASS]')
            pass
        else:
            print '[UPDATE]'
            #updateSQL = '''
            #    UPDATE user_image_info SET path = %s
            #        WHERE user_image_info.MD5 = %s
            #            AND user_image_info.user_id = %s;
            #'''
            updateSQL = '''
                UPDATE material_info
                    SET path = %s,
                        update_time = %s
                    WHERE material_info.MD5 = %s
            '''
            resultUpdate = MySqlCRUD.updateImpl(updateSQL, path, timeStamp, md5)
            log.debug(resultUpdate)
            log.debug(['DONE'])

    else:
        log.debug('[INSERT]')
        #insertSQL = '''
        #    INSERT INTO user_image_info (MD5, path, user_id)
        #        SELECT %s, %s, ID FROM user_info
        #            WHERE user_info.email = %s;
        #'''
        insertSQL = '''
            INSERT INTO user_image_info (MD5, user_id, image_source_type, update_time)
                SELECT %s, ID, %s, %s FROM user_info
                    WHERE user_info.tel = %s;
        '''
        resultInsert = MySqlCRUD.insertImpl(insertSQL, md5, '1', timeStamp, tel)
        log.debug('[UPLOADIMAGE][DONE] AFFECTED_ROW[' + str(resultInsert) +']')

@app.task
def uploadCollectionByQueue(md5, tel, vImgSourceType):
    insertSQL = '''
        INSERT INTO user_image_info (MD5, user_id, image_source_type, update_time)
            SELECT %s, ID, %s, %s FROM user_info
                WHERE user_info.tel = %s;
    '''
    timeStamp = time.time()
    log.debug(insertSQL, md5, vImgSourceType, timeStamp, tel)
    resultInsert = MySqlCRUD.insertImpl(insertSQL, md5, vImgSourceType, timeStamp, tel)
    log.debug('[UPLOADCOLLECTION][DONE] AFFECTED_ROW[' + str(resultInsert) +']')
    return resultInsert

@app.task
def removeCollectionByQueue(md5, tel, vImgSourceType):
    deleteSQL = '''
        DELETE user_image_info FROM user_image_info
            INNER JOIN user_info
            ON user_image_info.user_id = user_info.ID
            WHERE user_image_info.MD5 = %s AND user_info.tel = %s AND user_image_info.image_source_type = %s;
    '''
    log.debug(deleteSQL, md5, tel, vImgSourceType)
    resultInsert = MySqlCRUD.deleteImpl(deleteSQL, md5, tel, vImgSourceType)
    log.debug('[UPLOADCOLLECTION][DONE] AFFECTED_ROW[' + str(resultInsert) +']')
    return resultInsert

@app.task
def uploadAchievementByQueue(md5, path, tel, imgType):
    insertSQL = '''
        INSERT INTO achievement_info (MD5, path, user_id, type, update_time)
            SELECT %s, %s, ID, %s, %s FROM user_info
                WHERE user_info.tel = %s;
    '''
    timeStamp = time.time()
    log.debug(insertSQL, md5, path, imgType, timeStamp, tel)
    resultInsert = MySqlCRUD.insertImpl(insertSQL, md5, path, imgType, timeStamp, tel)
    log.debug('[UPLOADACHIEVEMENT][DONE] AFFECTED_ROW[' + str(resultInsert) +']')

@app.task
def updateMaterialInfoByQueue(md5, tel, category, name, material, size, weight, aperture, price, description, url):
    ######查询图片本身是否存在
    #查询语句，查询该图片md5是否存在记录；
    imgExitsSQL = '''
        SELECT material_info.MD5 FROM material_info
            WHERE material_info.MD5 = %s;
    '''
    imgExistsResult = MySqlCRUD.selectImpl(imgExitsSQL, md5)
    if imgExistsResult:
        updateSQL = '''
            UPDATE material_info
                SET category = %s,
                    name = %s,
                    material = %s,
                    size = %s,
                    weight = %s,
                    aperture = %s,
                    price = %s,
                    url = %s,
                    description = %s
                WHERE MD5 = %s;
        '''
        timeStamp = time.time()
        updateSQL2 = '''
            UPDATE user_image_info
                SET update_time = %s
                WHERE MD5 = %s;
        '''
        resultUpdate = MySqlCRUD.updateImpl(updateSQL, category, name, material, size, weight, aperture, price, url, description, md5)
        resultUpdate2 = MySqlCRUD.updateImpl(updateSQL2, timeStamp, md5)
        log.debug("[EXISTS]上传的图片已经存在,可以修改！")
    else:
        log.error("[UPDATE][MATERIAL][INFO]图片不存在")

@app.task
def deleteMaterialUserInfoByQueue(md5, tel):
    deleteSQL = '''
        DELETE user_image_info FROM user_image_info
            INNER JOIN user_info
            ON user_image_info.user_id = user_info.ID
            WHERE user_image_info.MD5 = %s AND user_info.tel = %s;
    '''
    resultDelete = MySqlCRUD.deleteImpl(deleteSQL, md5, tel)
    log.debug('deleteMaterialUserInfoByQueue' + str(resultDelete))
